﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using System.Web.UI.WebControls;
using eBusiness.App_DAL;
using eBusiness.Models;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace eBusiness.Areas.Admin.Controllers
{
    /// <summary>
    /// Controller for admin function of product
    /// </summary>
    [Authorize]
    public class ProductsController : Controller
    {
        private readonly UnitOfWork _unitOfWork = new UnitOfWork();

        //
        // GET: /Products/
        /// <summary>
        /// Get all product met the condition as fitler fields choosen
        /// </summary>
        /// <param name="filter">filter product fields</param>
        /// <param name="pageNumber">page number</param>
        /// <param name="pageSize">page size</param>
        /// <param name="categoryId">categoy of product</param>
        /// <param name="softField">soft field</param>
        /// <param name="softDir">soft directory</param>
        /// <returns>list of product</returns>
        public ViewResult Index(Product filter = null, int? pageNumber = 1, int? pageSize = 5, byte? categoryId = null, string softField = "CreatedDate", string softDir = "DESC")
        {
            //set current variable for filter

            ViewBag.CurrentCategoryID = Session["Category"] = categoryId.ToString();
            if (filter != null)
            {
                ViewBag.CurrentName = filter.Name;
                ViewBag.CurrentStatusID = Session["StatusID"] = filter.StatusID.ToString();
            }
            ViewBag.CurrentSoftField = softField;
            ViewBag.CurrentSoftDir = softDir;
            ViewBag.CurrentIsApproved = filter != null && filter.IsApproved;
            ViewBag.CurrentIsHot = filter != null && filter.IsHot;
            ViewBag.CurrentIsHomepage = filter != null && filter.IsHomepage;
            ViewBag.CurrentIsDiscount = filter != null && filter.IsDiscount;

            //reset data for dropdownlist for category, status, soft field and sorf directory

            ViewBag.Categories = new SelectList(_unitOfWork.CategoryRepository.Get(c => c.IsDeleted == false), "ID", "Name", Session["Category"]);
            ViewBag.StatusId = new SelectList(_unitOfWork.StatusRepository.Get(s => s.IsDeleted == false), "ID", "Name", Session["StatusID"]);
            var softdir = new List<ListItem> { new ListItem("Desc", "DESC"), new ListItem("Asc", "ASC") };
            //ViewBag.SoftDir = new SelectList(softdir, Session["SoftDir"]);
            ViewBag.SoftDir = new SelectList(softdir, softDir);

            var softFields = new List<ListItem>
                                 {
                                     new ListItem("Name", "Name"),
                                     new ListItem("Code", "Code"),
                                     new ListItem("Price", "Price"),
                                     new ListItem("Quantity", "Quantity"),
                                     new ListItem("Type", "Type"),
                                     new ListItem("Color", "Color"),
                                     new ListItem("Size", "Size"),
                                 };
            //ViewBag.SoftFields = new SelectList(softFields, Session["SoftField"]);
            ViewBag.SoftFields = new SelectList(softFields, softField);

            //apply filtering for get list product

            var filterExpressions = new Collection<Expression<Func<Product, bool>>>();

            Expression<Func<Product, bool>> expressionLeft = (product => product.IsDeleted == false);

            filterExpressions.Add(expressionLeft);

            //filter by category

            if (categoryId != null)
            {
                var category = _unitOfWork.CategoryRepository.GetByID(categoryId);
                expressionLeft = (product => product.Categories.FirstOrDefault(c => c.ID == categoryId).ID == categoryId);
                filterExpressions.Add(expressionLeft);
            }

            //filter by Ishot, IsHomepage, IsApproved, IsDiscount

            if (filter != null)
            {
                if (filter.IsHot)
                {
                    expressionLeft = (product => product.IsHot == filter.IsHot);
                    filterExpressions.Add(expressionLeft);
                }
                if (filter.IsApproved)
                {
                    expressionLeft = (product => product.IsApproved == filter.IsApproved);
                    filterExpressions.Add(expressionLeft);
                }
                if (filter.IsHomepage)
                {
                    expressionLeft = (product => product.IsHomepage == filter.IsHomepage);
                    filterExpressions.Add(expressionLeft);
                }
                if (filter.IsDiscount)
                {
                    expressionLeft = (product => product.IsDiscount == filter.IsDiscount);
                    filterExpressions.Add(expressionLeft);
                }
                if (filter.StatusID != null)
                {
                    expressionLeft = (product => product.StatusID == filter.StatusID);
                    filterExpressions.Add(expressionLeft);
                }
                if (filter.Name != null)
                {
                    expressionLeft = (product => product.Name.Contains(filter.Name));
                    filterExpressions.Add(expressionLeft);
                }
            }

            //ordering the result of filter

            Func<IQueryable<Product>, IOrderedQueryable<Product>> orderby = (product => product.OrderByDescending(p => p.CreatedDate));
            switch (softDir.ToUpper())
            {
                case "ASC":
                    if (softField != null && !string.IsNullOrEmpty(softField))
                    {
                        switch (softField)
                        {
                            case "Price":
                                orderby = (product => product.OrderBy(p => p.Price));
                                break;
                            case "Name":
                                orderby = (product => product.OrderBy(p => p.Name));
                                break;
                            case "Code":
                                orderby = (product => product.OrderBy(p => p.Code));
                                break;
                            case "Quantity":
                                orderby = (product => product.OrderBy(p => p.QuantityInStock));
                                break;
                            case "Type":
                                orderby = (product => product.OrderBy(p => p.Type));
                                break;
                            case "Size":
                                orderby = (product => product.OrderBy(p => p.Size));
                                break;
                            case "Color":
                                orderby = (product => product.OrderBy(p => p.Color));
                                break;
                            default:
                                orderby = (product => product.OrderBy(p => p.CreatedDate));
                                break;
                        }
                    }
                    break;
                case "DESC":
                    if (softField != null && !string.IsNullOrEmpty(softField))
                    {
                        switch (softField)
                        {
                            case "Price":
                                orderby = (product => product.OrderByDescending(p => p.Price));
                                break;
                            case "Name":
                                orderby = (product => product.OrderByDescending(p => p.Name));
                                break;
                            case "Code":
                                orderby = (product => product.OrderByDescending(p => p.Code));
                                break;
                            case "Quantity":
                                orderby = (product => product.OrderByDescending(p => p.QuantityInStock));
                                break;
                            case "Type":
                                orderby = (product => product.OrderByDescending(p => p.Type));
                                break;
                            case "Size":
                                orderby = (product => product.OrderByDescending(p => p.Size));
                                break;
                            case "Color":
                                orderby = (product => product.OrderByDescending(p => p.Color));
                                break;
                            default:
                                orderby = (product => product.OrderByDescending(p => p.CreatedDate));
                                break;
                        }
                    }
                    break;
            }

            //prepare to paging

            var products = _unitOfWork.ProductRepository.Get(filters: filterExpressions, orderby: orderby);

            long numberOfRecord = products.Count();

            long numberOfPage = numberOfRecord % pageSize == 0
                                   ? numberOfRecord / pageSize.Value
                                   : numberOfRecord / pageSize.Value + 1;

            ViewBag.NumberOfPage = numberOfPage;
            ViewBag.CurrentPage = pageNumber;

            products = _unitOfWork.ProductRepository.Get(filters: filterExpressions, orderby: orderby, pageSize: pageSize, pageNumber: pageNumber);
            return View(products.ToList());
        }

        public string GetProductLink(string pageNumber)
        {
            var link = "/Admin/Products/Index?";
            if (!string.IsNullOrEmpty(ViewBag.CurrentCategoryID))
            {
                link += "&categoryId=" + ViewBag.CurrentCategoryID;
            }
            if (!string.IsNullOrEmpty(ViewBag.CurrentStatusID))
            {
                link += "&StatusID=" + ViewBag.CurrentStatusID;
            }
            if (!string.IsNullOrEmpty(ViewBag.CurrentSoftField))
            {
                link += "&softField=" + ViewBag.CurrentSoftField;
            }
            if (!string.IsNullOrEmpty(ViewBag.CurrentSoftDir))
            {
                link += "&softDir=" + ViewBag.CurrentSoftDir;
            }
            if (!string.IsNullOrEmpty(pageNumber))
            {
                link += "&pageNumber=" + pageNumber;
            }
            return link;
        }

        //
        // GET: /Products/Details/5
        /// <summary>
        /// Get product details of specific product base on which ID
        /// </summary>
        /// <param name="id">ID of product</param>
        /// <returns>Product with details</returns>
        public ViewResult Details(long id)
        {
            Product product = _unitOfWork.ProductRepository.GetByID(id);
            return View(product);
        }

        //
        // GET: /Products/Create
        /// <summary>
        /// Create new product
        /// </summary>
        /// <returns>view of creating product</returns>
        public ActionResult Create()
        {
            ViewBag.Status = new SelectList(_unitOfWork.StatusRepository.Get(s => s.IsDeleted == false), "ID", "Name");
            ViewBag.Manufacture = new SelectList(_unitOfWork.ManufactureRepository.Get(s => s.IsDeleted == false), "ID", "Name");
            return View();
        }

        //
        // POST: /Products/Create

        /// <summary>
        /// create new product
        /// </summary>
        /// <param name="product">product fields entered</param>
        /// <param name="categoryIds">category of product</param>
        /// <returns>new product</returns>
        [HttpPost]
        [Authorize]
        public ActionResult Create(Product product, string categoryIds = null)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    product.CreatedDate = DateTime.Now;
                    product.LastModifiedDate = DateTime.Now;
                    var membershipUser = Membership.GetUser();
                    if (membershipUser != null)
                    {
                        var providerUserKey = membershipUser.ProviderUserKey;
                        if (providerUserKey != null)
                        {
                            product.CreatedBy = (Guid)providerUserKey;
                            product.LastModifiedBy = (Guid)providerUserKey;
                        }
                    }

                    //save category

                    if (categoryIds != null && !string.IsNullOrEmpty(categoryIds.TrimEnd(',')))
                    {
                        var categories = categoryIds.Split(',');
                        foreach (var cat in categories)
                        {
                            if (string.IsNullOrEmpty(cat.TrimEnd(','))) continue;
                            var category = _unitOfWork.CategoryRepository.GetByID(Convert.ToInt32(cat.TrimEnd(',')));
                            if (category != null)
                            {
                                product.Categories.Add(category);
                                category.Products.Add(product);
                            }
                        }
                    }

                    //save file uploaded for thumb image and product images

                    var imagesLink = string.Empty;
                    for (int i = 0; i < Request.Files.Count; i++)
                    {
                        var hpf = Request.Files[i] as HttpPostedFileBase;
                        if (hpf != null)
                        {
                            if (hpf.ContentLength == 0)
                                continue;

                            string savedFileName = AppDomain.CurrentDomain.BaseDirectory +
                                                   "\\Content\\images\\products\\" + Path.GetFileName(hpf.FileName);
                            hpf.SaveAs(savedFileName);

                            imagesLink += "/Content/images/products/" + Path.GetFileName(hpf.FileName) + ":";

                            if (i.ToString() == product.ThumbImage)
                            {
                                product.ThumbImage = "/Content/images/products/" + Path.GetFileName(hpf.FileName);
                            }
                        }
                        product.Images = imagesLink;
                    }

                    //save to database

                    _unitOfWork.ProductRepository.Insert(product);
                    _unitOfWork.Save();
                    return RedirectToAction("Index");
                }
                ViewBag.Status = new SelectList(_unitOfWork.StatusRepository.Get(s => s.IsDeleted == false), "ID", "Name", product.StatusID);
                ViewBag.Manufacture = new SelectList(_unitOfWork.ManufactureRepository.Get(s => s.IsDeleted == false), "ID", "Name", product.ManufactureID);
                return View(product);
            }
            catch (Exception)
            {
                return View("Error");
            }
        }

        //
        // GET: /Products/Edit/5

        public ActionResult Edit(long id)
        {
            Product product = _unitOfWork.ProductRepository.GetByID(id);
            ViewBag.Status = new SelectList(_unitOfWork.StatusRepository.Get(s => s.IsDeleted == false), "ID", "Name", product.StatusID);
            ViewBag.Manufacture = new SelectList(_unitOfWork.ManufactureRepository.Get(s => s.IsDeleted == false), "ID", "Name", product.ManufactureID);
            return View(product);
        }

        //
        // POST: /Products/Edit/5
        /// <summary>
        /// Edit existed product
        /// </summary>
        /// <param name="product">product changed</param>
        /// <param name="categoryIds">category of product</param>
        /// <returns>updated product</returns>
        [HttpPost]
        public ActionResult Edit(Product product, string categoryIds = null)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    //var oldProduct = _unitOfWork.ProductRepository.GetByID(product.ID);
                    //foreach (var category in oldProduct.Categories)
                    //{
                    //    product.Categories.Add(category);
                    //}

                    product.LastModifiedDate = DateTime.Now;
                    var membershipUser = Membership.GetUser();
                    if (membershipUser != null)
                    {
                        var providerUserKey = membershipUser.ProviderUserKey;
                        if (providerUserKey != null)
                        {
                            product.LastModifiedBy = (Guid)providerUserKey;
                        }
                    }

                    //save file uploaded for thumb image and product images

                    var imagesLink = string.Empty;
                    for (int i = 0; i < Request.Files.Count; i++)
                    {
                        var hpf = Request.Files[i] as HttpPostedFileBase;
                        if (hpf != null)
                        {
                            if (hpf.ContentLength == 0)
                                continue;

                            string savedFileName = AppDomain.CurrentDomain.BaseDirectory +
                                                   "\\Content\\images\\products\\" + Path.GetFileName(hpf.FileName);
                            hpf.SaveAs(savedFileName);

                            imagesLink += "/Content/images/products/" + Path.GetFileName(hpf.FileName) + ":";

                            if (i.ToString() == product.ThumbImage)
                            {
                                product.ThumbImage = "/Content/images/products/" + Path.GetFileName(hpf.FileName);
                            }
                        }
                        product.Images = imagesLink;
                    }

                    //save product changes to database

                    _unitOfWork.ProductRepository.Update(product);

                    //save category

                    if (categoryIds != null && !string.IsNullOrEmpty(categoryIds.TrimEnd(',')))
                    {
                        var categories = categoryIds.Split(',');
                        foreach (var cat in _unitOfWork.CategoryRepository.Get(c => c.IsDeleted == false, includeProperties: "Products"))
                        {
                            cat.Products.Remove(_unitOfWork.ProductRepository.GetByID(product.ID));
                            _unitOfWork.CategoryRepository.Update(cat);
                        }

                        foreach (var cat in categories)
                        {
                            if (string.IsNullOrEmpty(cat.TrimEnd(','))) continue;
                            var category = _unitOfWork.CategoryRepository.GetByID(Convert.ToInt32(cat.TrimEnd(',')));
                            if (category != null)
                            {
                                product.Categories.Add(category);
                                category.Products.Add(product);
                            }
                        }
                    }

                    _unitOfWork.Save();
                    return RedirectToAction("Index");
                }
                ViewBag.Status = new SelectList(_unitOfWork.StatusRepository.Get(s => s.IsDeleted == false), "ID", "Name", product.StatusID);
                ViewBag.Manufacture = new SelectList(_unitOfWork.ManufactureRepository.Get(s => s.IsDeleted == false), "ID", "Name", product.ManufactureID);
                return View(product);
            }
            catch (Exception)
            {
                return View("Error");
            }
        }

        //
        // GET: /Products/Delete/5

        public ActionResult Delete(long id)
        {
            Product product = _unitOfWork.ProductRepository.GetByID(id);
            return View(product);
        }

        //
        // POST: /Products/Delete/5
        /// <summary>
        /// delete existed product
        /// </summary>
        /// <param name="id">Id of product need to delete</param>
        /// <returns>delete product from database</returns>
        [HttpPost, ActionName("Delete")]
        public ActionResult DeleteConfirmed(long id)
        {
            try
            {

                Product product = _unitOfWork.ProductRepository.GetByID(id);
                product.IsDeleted = true;
                product.LastModifiedDate = DateTime.Now;
                var membershipUser = Membership.GetUser();
                if (membershipUser != null)
                {
                    var providerUserKey = membershipUser.ProviderUserKey;
                    if (providerUserKey != null)
                    {
                        product.LastModifiedBy = (Guid)providerUserKey;
                    }
                }
                //db.Products.Remove(product);

                _unitOfWork.ProductRepository.Update(product);
                _unitOfWork.Save();

                return RedirectToAction("Index");

            }
            catch (Exception)
            {
                return View("Error");
            }
        }

        /// <summary>
        /// approve for product
        /// </summary>
        /// <param name="id">id of product</param>
        /// <returns></returns>
        public ActionResult Approved(long id)
        {
            try
            {
                Product product = _unitOfWork.ProductRepository.GetByID(id);
                product.IsApproved = !product.IsApproved;
                product.ApprovedDate = DateTime.Now;
                var membershipUser = Membership.GetUser();
                if (membershipUser != null)
                {
                    var providerUserKey = membershipUser.ProviderUserKey;
                    if (providerUserKey != null)
                    {
                        product.ApprovedBy = (Guid)providerUserKey;
                    }
                }
                _unitOfWork.ProductRepository.Update(product);
                _unitOfWork.Save();
                return View("Details", product);
            }
            catch (Exception)
            {
                return View("Error");
            }
        }

        public ActionResult Export(Product filter = null, byte? categoryId = null)
        {
            var filterExpressions = new Collection<Expression<Func<Product, bool>>>();

            Expression<Func<Product, bool>> expressionLeft = (product => product.IsDeleted == false);

            filterExpressions.Add(expressionLeft);

            //filter by category

            if (categoryId != null)
            {
                var category = _unitOfWork.CategoryRepository.GetByID(categoryId);
                expressionLeft = (product => product.Categories.FirstOrDefault(c => c.ID == categoryId).ID == categoryId);
                filterExpressions.Add(expressionLeft);
            }

            //filter by Ishot, IsHomepage, IsApproved, IsDiscount

            if (filter != null)
            {
                if (filter.IsHot)
                {
                    expressionLeft = (product => product.IsHot == filter.IsHot);
                    filterExpressions.Add(expressionLeft);
                }
                if (filter.IsApproved)
                {
                    expressionLeft = (product => product.IsApproved == filter.IsApproved);
                    filterExpressions.Add(expressionLeft);
                }
                if (filter.IsHomepage)
                {
                    expressionLeft = (product => product.IsHomepage == filter.IsHomepage);
                    filterExpressions.Add(expressionLeft);
                }
                if (filter.IsDiscount)
                {
                    expressionLeft = (product => product.IsDiscount == filter.IsDiscount);
                    filterExpressions.Add(expressionLeft);
                }
                if (filter.StatusID != null)
                {
                    expressionLeft = (product => product.StatusID == filter.StatusID);
                    filterExpressions.Add(expressionLeft);
                }
            }

            var products = _unitOfWork.ProductRepository.Get(filters: filterExpressions).ToList();
            var fileName = @"~/Content/excels/export/products_" + DateTime.Today.ToString("dd_MM_yyyy") + ".xls";
            var filePath = Server.MapPath(fileName);
            ExportData(products, filePath);
            return File(filePath, "application/pdf", "products.xls");
        }

        public ActionResult Import()
        {
            return View();
        }

        [HttpPost]
        [Authorize]
        public ActionResult Import(string file)
        {
            //upload file to server
            string savedFileName = "";
            for (int i = 0; i < Request.Files.Count; i++)
            {
                var hpf = Request.Files[i] as HttpPostedFileBase;
                if (hpf != null)
                {
                    if (hpf.ContentLength == 0)
                        continue;

                    savedFileName = AppDomain.CurrentDomain.BaseDirectory +
                                           "\\Content\\excels\\" + Path.GetFileName(hpf.FileName) + "_" + DateTime.Today.ToString("yyyy_MM_dd");
                    hpf.SaveAs(savedFileName);
                }
            }

            //Open the Excel workbook.
            using (SpreadsheetDocument document =
              SpreadsheetDocument.Open(savedFileName, true))
            {
                //References to the workbook and Shared String Table.
                Workbook workBook = document.WorkbookPart.Workbook;
                IEnumerable<Sheet> workSheets = workBook.Descendants<Sheet>();
                SharedStringTable sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable;

                //Reference to Excel Worksheet with product data.
                string prodID = workSheets.First(s => s.Name == @"Product").Id;
                var custSheet = (WorksheetPart)document.WorkbookPart.GetPartById(prodID);

                //Load product data to business object.
                List<Product> products = LoadProductFormExcel(custSheet.Worksheet, sharedStrings);

                var loginUser = new Guid();
                var membershipUser = Membership.GetUser();
                if (membershipUser != null)
                {
                    var providerUserKey = membershipUser.ProviderUserKey;
                    if (providerUserKey != null)
                    {
                        loginUser = (Guid)providerUserKey;
                    }
                }

                foreach (var product in products)
                {
                    product.CreatedDate = DateTime.Now;
                    product.LastModifiedDate = DateTime.Now;
                    product.CreatedBy = loginUser;
                    product.LastModifiedBy = loginUser;

                    var existed = _unitOfWork.ProductRepository.Get(p => p.Code == product.Code).Count() > 0;

                    if (existed)
                    {
                        var existedProduct = _unitOfWork.ProductRepository.Get(p => p.Code == product.Code).FirstOrDefault();
                        if (existedProduct != null)
                        {
                            existedProduct.LastModifiedDate = DateTime.Now;
                            existedProduct.Name = product.Name;
                            existedProduct.Price = product.Price;
                            existedProduct.Description = product.Description;
                            existedProduct.Warranty = product.Warranty;
                            existedProduct.Manufacture = product.Manufacture;
                            existedProduct.IsDiscount = product.IsDiscount;
                            existedProduct.DiscountByPercent = product.DiscountByPercent;
                            existedProduct.DiscountByNumber = product.DiscountByNumber;

                            _unitOfWork.ProductRepository.Update(existedProduct);
                        }
                    }
                    else
                    {
                        _unitOfWork.ProductRepository.Insert(product);


                    }
                }
                _unitOfWork.Save();
            }
            return View();
        }

        protected List<Product> LoadProductFormExcel(Worksheet worksheet, SharedStringTable sharedString)
        {

            //Initialize the product list.
            List<Product> result = new List<Product>();

            //LINQ query to skip first row with column names.
            IEnumerable<Row> dataRows =
              from row in worksheet.Descendants<Row>()
              where row.RowIndex > 1
              select row;

            foreach (Row row in dataRows)
            {
                //LINQ query to return the row's cell values.
                //Where clause filters out any cells that do not contain a value.
                //Select returns the value of a cell unless the cell contains
                //  a Shared String.
                //If the cell contains a Shared String, its value will be a 
                //  reference id which will be used to look up the value in the 
                //  Shared String table.
                IEnumerable<String> textValues =
                  from cell in row.Descendants<Cell>()
                  where cell.CellValue != null
                  select
                    (cell.DataType != null
                      && cell.DataType.HasValue
                      && cell.DataType == CellValues.SharedString
                    ? sharedString.ChildElements[
                      int.Parse(cell.CellValue.InnerText)].InnerText
                    : cell.CellValue.InnerText)
                  ;

                //Check to verify the row contained data.
                if (textValues.Count() > 0)
                {
                    //Create a product and add it to the list.
                    var textArray = textValues.ToArray();
                    Product product = new Product();
                    product.Name = textArray[0];
                    product.Code = textArray[1];
                    product.Price = Convert.ToDecimal(textArray[2]);
                    product.Description = textArray[3];
                    product.Warranty = textArray[4];
                    var manuf = _unitOfWork.ManufactureRepository.Get(m => m.Name == textArray[5]).FirstOrDefault();
                    if (manuf != null) product.ManufactureID = manuf.ID;
                    product.IsDiscount = textArray[6] == "0";
                    product.DiscountByPercent = Convert.ToByte(textArray[7]);
                    product.DiscountByNumber = Convert.ToDecimal(textArray[8]);
                    result.Add(product);
                }
                else
                {
                    //If no cells, then you have reached the end of the table.
                    break;
                }
            }

            //Return populated list of customers.
            return result;

        }

        protected void ExportData(List<Product> products, string destination)
        {
            using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = workbook.AddWorkbookPart();

                workbook.WorkbookPart.Workbook = new Workbook { Sheets = new Sheets() };

                //foreach (var product in products)
                //{
                var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                sheetPart.Worksheet = new Worksheet(sheetData);

                Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId =
                        sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }

                Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = "Product" };
                sheets.Append(sheet);

                Row headerRow = new Row();

                List<String> columns = new List<string>()
                                               {
                                                   "Name","Code","Price","Description","Warranty","Manufacture","IsDiscount","DiscountByPercent","DiscountByNumber"
                                               };
                foreach (var column in columns)
                {
                    //columns.Add(column.ColumnName);

                    Cell cell = new Cell();
                    cell.DataType = CellValues.String;
                    cell.CellValue = new CellValue(column);
                    headerRow.AppendChild(cell);
                }


                sheetData.AppendChild(headerRow);

                foreach (var product in products)
                {
                    Row newRow = new Row();
                    //foreach (String col in columns)
                    //{
                    Cell cell = new Cell();
                    cell.DataType = CellValues.String;
                    cell.CellValue = new CellValue(product.Name); //
                    newRow.AppendChild(cell);

                    cell = new Cell();
                    cell.CellValue = new CellValue(product.Code); //
                    newRow.AppendChild(cell);

                    cell = new Cell();
                    cell.CellValue = new CellValue(product.Price.ToString()); //
                    newRow.AppendChild(cell);

                    cell = new Cell();
                    cell.CellValue = new CellValue(product.Description); //
                    newRow.AppendChild(cell);

                    cell = new Cell();
                    cell.CellValue = new CellValue(product.Warranty); //
                    newRow.AppendChild(cell);

                    cell = new Cell();
                    cell.CellValue = new CellValue(product.Manufacture.Name); //
                    newRow.AppendChild(cell);

                    cell = new Cell();
                    var isDiscount = product.IsDiscount == false ? "0" : "1";
                    cell.CellValue = new CellValue(isDiscount); //
                    newRow.AppendChild(cell);

                    cell = new Cell();
                    cell.CellValue = new CellValue(product.DiscountByPercent.ToString()); //
                    newRow.AppendChild(cell);

                    cell = new Cell();
                    cell.CellValue = new CellValue(product.DiscountByNumber.ToString()); //
                    newRow.AppendChild(cell);
                    //}

                    sheetData.AppendChild(newRow);
                }

                //}
            }
        }

        protected override void Dispose(bool disposing)
        {
            _unitOfWork.Dispose();
        }
    }
}